package com.ansion.basedb.database;

import androidx.annotation.NonNull;

import com.ansion.basedb.database.sql.SQLJoinType;
import com.ansion.basedb.database.sql.SQLRow;
import com.ansion.basetool.preconditions.Preconditions;
import com.ansion.utils.ArrayUtil;
import com.ansion.utils.ValueSafeTransfer;

import java.util.Collection;
import java.util.List;
import java.util.Locale;

public class SqlQueryUtil {


    @NonNull
    public static String buildSqlStringEqual(@NonNull String columnName, @NonNull String value) {
        return columnName + " = '" + value + "'";
    }

    @NonNull
    public static String buildSqlNumberEqual(@NonNull String columnName, @NonNull Object value) {
        return columnName + " = " + value;
    }

    @NonNull
    public static String buildSqlLessOrEqual(@NonNull String columnName, Object value) {
        return columnName + " <= " + value;
    }

    @NonNull
    public static String buildSqlLess(@NonNull String columnName, Object value) {
        return columnName + " < " + value;
    }

    @NonNull
    public static String buildSqlBigger(@NonNull String columnName, Object value) {
        return columnName + " > " + value;
    }

    @NonNull
    public static String buildSqlCount(@NonNull String columnName, @NonNull String refName) {
        return String.format(Locale.ENGLISH, "count(%s) as %s", columnName, refName);
    }

    @NonNull
    public static String buildSqlSum(@NonNull String columnName, @NonNull String refName) {
        return String.format(Locale.ENGLISH, "sum(%s) as %s", columnName, refName);
    }

    @NonNull
    public static String buildSqlQueryColumn(@NonNull String tableName, @NonNull String columnName) {
        return tableName + "." + columnName;
    }


    @NonNull
    public static String buildSqlLike(@NonNull String columnName, @NonNull String value) {
        return columnName + " LIKE '%" + value + "%'";
    }

    @NonNull
    public static String buildSqlInForStringList(@NonNull String columnName, @NonNull Collection<String> list) {
        return columnName + " IN (" + combineSqlInString(list) + ")";
    }

    @NonNull
    public static <T extends Object> String buildSqlInForNumberList(@NonNull String columnName, @NonNull Collection<T> list) {
        return columnName + " IN (" + combineSqlIn(list) + ")";
    }

    @NonNull
    public static String buildSqlIn(@NonNull String columnName, Object... types) {
        return columnName + " IN (" + ArrayUtil.combine(types) + ")";
    }

    @NonNull
    public static String buildSqlIn(@NonNull String columnName, String... values) {
        return columnName + " IN (" + combineSqlInString(values) + ")";
    }

    @NonNull
    public static String buildSqlJoin(@SQLJoinType int joinType, @NonNull String table1Name, @NonNull String table1Column, @NonNull String table2Name, @NonNull String table2Column) {
        StringBuilder joinBuilder = new StringBuilder();
        String joinStr = "INNER JOIN";
        if (joinType == SQLJoinType.TYPE_LEFT_JOIN) {
            joinStr = "LEFT JOIN";
        } else if (joinType == SQLJoinType.TYPE_RIGHT_JOIN) {
            joinStr = "RIGHT JOIN";
        }
        joinBuilder.append(" ").append(joinStr).append(" ").append(table2Name).append(" ON ").append(String.format("%s.%s", table1Name, table1Column))
                .append(" = ").append(String.format("%s.%s", table2Name, table2Column));
        return joinBuilder.toString();
    }

    @NonNull
    public static String buildSqlMatch(@NonNull String columnName, @NonNull String value) {
        return columnName + " MATCH '" + value + "'";
    }

    @NonNull
    public static String buildSqlOrderBy(@NonNull String columnName, boolean desc) {
        return " ORDER BY " + columnName + " " + (desc ? "DESC" : "ASC");
    }

    @NonNull
    public static String buildMoreSqlOrderBy(@NonNull String columnName, boolean desc) {
        return "," + columnName + " " + (desc ? "DESC" : "ASC");
    }

    @NonNull
    public static String buildOrderByOnly(@NonNull String columnName, boolean desc) {
        return columnName + " " + (desc ? "DESC" : "ASC");
    }

    public static String buildSqlLimit(int limit, int offset) {
        String limitParam = "";
        if (limit > 0) {
            limitParam += " LIMIT ";
            if (offset > 0) {
                limitParam += (offset + ",");
            }
            limitParam += limit;
        }
        return limitParam;
    }

    @NonNull
    public static String buildSqlSelect(@NonNull Object... columns) {
        Preconditions.checkNotEmpty(columns);
        return ArrayUtil.combine(columns);
    }

    public static String buildMax(@NonNull String column, @NonNull String alias) {
        return String.format(Locale.ENGLISH, "max(%s) as %s", column, alias);
    }



    @NonNull
    private static String combineSqlInString(@NonNull Collection<String> list) {
        if (list.isEmpty())
            return "";
        StringBuilder sb = new StringBuilder();
        for (String t : list) {
            sb.append("'").append(t).append("'").append(",");
        }
        //删去最后一个字符
        sb.deleteCharAt(sb.toString().length() - 1);
        return sb.toString();
    }

    @NonNull
    private static <T extends Object> String combineSqlIn(@NonNull Collection<T> list) {
        if (list.isEmpty())
            return "";
        StringBuilder sb = new StringBuilder();
        for (Object t : list) {
            sb.append(t).append(",");
        }
        //删去最后一个字符
        sb.deleteCharAt(sb.toString().length() - 1);
        return sb.toString();
    }


    @NonNull
    private static String combineSqlInString(@NonNull String... params) {
        if (params.length == 0)
            return "";
        StringBuilder sb = new StringBuilder();
        for (String t : params) {
            sb.append("'").append(t).append("'").append(",");
        }
        //删去最后一个字符
        sb.deleteCharAt(sb.toString().length() - 1);
        return sb.toString();
    }

    @NonNull
    public static String generateWhereClause(@NonNull List<String> filedNameList) {
        StringBuilder sb = new StringBuilder();
        ValueSafeTransfer.iterateAll(filedNameList, (position, filedName) -> {
            sb.append(position > 0 ? " AND " : " ").append(filedName).append(" = ? ");
        });
        return sb.toString();
    }

    @NonNull
    public static String[] generateWhereArgs(@NonNull List<String> filedValueList, @NonNull SQLRow row) {
        String[] args = new String[filedValueList.size()];
        ValueSafeTransfer.iterateAll(filedValueList, (position, filedValue) -> {
            args[position] = row.getAsString(filedValue);
        });
        return args;
    }

}
